Excel - Pivot Table
A powerful feature of Excel is a pivot table, this allows you to quickly look at a summary of data, the example below looks at Antibiotic prescribing by GP, and is based on a search by drug class - Penicillins prescribed in the last month.
The report has already generated, and the THERAPY.TXT imported into Excel.
- Open the THERAPY.TXT file.
See - Excel - Import TXT File. - Select the Select All button, to create the import for your pivot table.
All the data highlights.
- From the Toolbar Ribbon, select the Insert option, then Pivot Table.
The Create Pivot Table wizard displays.
- The input range populates automatically, and you can choose to place the pivot table into a new worksheet (default) or the existing one.
Select OK to proceed.
The Pivot Table options display.
- On the right the Pivot Table Field List includes all the column headings from your spreadsheet.
The Pivot Table area on the left updates, as you add in fields.
Here, we want to look at prescribing by clinicians, drag Clinician into the Column Labels box.
The Clinicians now display along the top of the page as Column Headings.
- Next, we want to display the drugs prescribed by each clinician.
Select Name (this is the Drug Name), and drag into the Row Labels.
The view refreshes and the Drug Names display as Rows.
-
The Clinicians and Drug Names show on the table, and we need a count of each time the clinician prescribed the drug.
If we select the Event Date this counts the number of dates that each Clinician prescribed each drug.
Select Event Date and drag into the Values box.
The pivot table refreshes, and the counts displays.
- Optionally you can:
- Apply a Filter to the Clinicians
Select the drop down and remove the tick from, for example, Federated Locum and Blank. - Insert a Graph
Select Insert on the Toolbar Ribbon.
Choose the chart type, for example, Column.
Select what sort of Column graph, for example, 2-D Column.
- Apply a Filter to the Clinicians
